{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#算法一：计算总分并排名"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>student</th>\n",
       "      <th>Chinese</th>\n",
       "      <th>Mathematics</th>\n",
       "      <th>English</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A1</td>\n",
       "      <td>71</td>\n",
       "      <td>72</td>\n",
       "      <td>89</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A2</td>\n",
       "      <td>67</td>\n",
       "      <td>81</td>\n",
       "      <td>86</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A3</td>\n",
       "      <td>60</td>\n",
       "      <td>74</td>\n",
       "      <td>88</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A4</td>\n",
       "      <td>78</td>\n",
       "      <td>90</td>\n",
       "      <td>85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>A5</td>\n",
       "      <td>70</td>\n",
       "      <td>90</td>\n",
       "      <td>85</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  student  Chinese  Mathematics  English\n",
       "0      A1       71           72       89\n",
       "1      A2       67           81       86\n",
       "2      A3       60           74       88\n",
       "3      A4       78           90       85\n",
       "4      A5       70           90       85"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_df=pd.read_csv('score.csv')\n",
    "data_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>student</th>\n",
       "      <th>Chinese</th>\n",
       "      <th>Mathematics</th>\n",
       "      <th>English</th>\n",
       "      <th>total</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A1</td>\n",
       "      <td>71</td>\n",
       "      <td>72</td>\n",
       "      <td>89</td>\n",
       "      <td>232</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A2</td>\n",
       "      <td>67</td>\n",
       "      <td>81</td>\n",
       "      <td>86</td>\n",
       "      <td>234</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A3</td>\n",
       "      <td>60</td>\n",
       "      <td>74</td>\n",
       "      <td>88</td>\n",
       "      <td>222</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A4</td>\n",
       "      <td>78</td>\n",
       "      <td>90</td>\n",
       "      <td>85</td>\n",
       "      <td>253</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>A5</td>\n",
       "      <td>70</td>\n",
       "      <td>90</td>\n",
       "      <td>85</td>\n",
       "      <td>245</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  student  Chinese  Mathematics  English  total\n",
       "0      A1       71           72       89    232\n",
       "1      A2       67           81       86    234\n",
       "2      A3       60           74       88    222\n",
       "3      A4       78           90       85    253\n",
       "4      A5       70           90       85    245"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_df['total']=data_df.sum(axis=1, numeric_only=True)\n",
    "data_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>student</th>\n",
       "      <th>Chinese</th>\n",
       "      <th>Mathematics</th>\n",
       "      <th>English</th>\n",
       "      <th>total</th>\n",
       "      <th>rank</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>A9</td>\n",
       "      <td>80</td>\n",
       "      <td>91</td>\n",
       "      <td>92</td>\n",
       "      <td>263</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>A15</td>\n",
       "      <td>74</td>\n",
       "      <td>89</td>\n",
       "      <td>91</td>\n",
       "      <td>254</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A4</td>\n",
       "      <td>78</td>\n",
       "      <td>90</td>\n",
       "      <td>85</td>\n",
       "      <td>253</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>A29</td>\n",
       "      <td>78</td>\n",
       "      <td>88</td>\n",
       "      <td>85</td>\n",
       "      <td>251</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>A46</td>\n",
       "      <td>72</td>\n",
       "      <td>89</td>\n",
       "      <td>88</td>\n",
       "      <td>249</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   student  Chinese  Mathematics  English  total  rank\n",
       "8       A9       80           91       92    263     1\n",
       "14     A15       74           89       91    254     2\n",
       "3       A4       78           90       85    253     3\n",
       "28     A29       78           88       85    251     4\n",
       "45     A46       72           89       88    249     5"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 总分排序\n",
    "data_df = data_df.sort_values(by='total',ascending=False)\n",
    "# 设置名次\n",
    "data_df['rank']=np.arange(1,len(data_df)+1)\n",
    "data_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([ 7., 17., 17.,  7.])"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 级别百分比\n",
    "level_percent=np.array([0.15,0.35,0.35,0.15])\n",
    "# 级别人数\n",
    "level_number= level_percent * len(data_df)\n",
    "# 级别人数约数\n",
    "level_number = np.round(level_number)\n",
    "level_number"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[7.0, 24.0, 41.0, 48.0]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rank_seq=[]\n",
    "# 累加级别人数，得到级别名次\n",
    "s=0\n",
    "for i in level_number:\n",
    "    s=s+i\n",
    "    rank_seq.append(s)\n",
    "rank_seq"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>student</th>\n",
       "      <th>Chinese</th>\n",
       "      <th>Mathematics</th>\n",
       "      <th>English</th>\n",
       "      <th>total</th>\n",
       "      <th>rank</th>\n",
       "      <th>level</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>A9</td>\n",
       "      <td>80</td>\n",
       "      <td>91</td>\n",
       "      <td>92</td>\n",
       "      <td>263</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>A15</td>\n",
       "      <td>74</td>\n",
       "      <td>89</td>\n",
       "      <td>91</td>\n",
       "      <td>254</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A4</td>\n",
       "      <td>78</td>\n",
       "      <td>90</td>\n",
       "      <td>85</td>\n",
       "      <td>253</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>A29</td>\n",
       "      <td>78</td>\n",
       "      <td>88</td>\n",
       "      <td>85</td>\n",
       "      <td>251</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>A46</td>\n",
       "      <td>72</td>\n",
       "      <td>89</td>\n",
       "      <td>88</td>\n",
       "      <td>249</td>\n",
       "      <td>5</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   student  Chinese  Mathematics  English  total  rank  level\n",
       "8       A9       80           91       92    263     1    NaN\n",
       "14     A15       74           89       91    254     2    NaN\n",
       "3       A4       78           90       85    253     3    NaN\n",
       "28     A29       78           88       85    251     4    NaN\n",
       "45     A46       72           89       88    249     5    NaN"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_df['level'] = np.nan\n",
    "data_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\C.Leclerc\\AppData\\Local\\Temp\\ipykernel_35744\\1660795134.py:4: FutureWarning: ChainedAssignmentError: behaviour will change in pandas 3.0!\n",
      "You are setting values through chained assignment. Currently this works in certain cases, but when using Copy-on-Write (which will become the default behaviour in pandas 3.0) this will never work to update the original DataFrame or Series, because the intermediate object on which we are setting values will behave as a copy.\n",
      "A typical example is when you are setting values in a column of a DataFrame, like:\n",
      "\n",
      "df[\"col\"][row_indexer] = value\n",
      "\n",
      "Use `df.loc[row_indexer, \"col\"] = values` instead, to perform the assignment in a single step and ensure this keeps updating the original `df`.\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "\n",
      "  data_df['level'][i]='优'\n",
      "C:\\Users\\C.Leclerc\\AppData\\Local\\Temp\\ipykernel_35744\\1660795134.py:4: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  data_df['level'][i]='优'\n",
      "C:\\Users\\C.Leclerc\\AppData\\Local\\Temp\\ipykernel_35744\\1660795134.py:4: FutureWarning: Setting an item of incompatible dtype is deprecated and will raise an error in a future version of pandas. Value '优' has dtype incompatible with float64, please explicitly cast to a compatible dtype first.\n",
      "  data_df['level'][i]='优'\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>student</th>\n",
       "      <th>Chinese</th>\n",
       "      <th>Mathematics</th>\n",
       "      <th>English</th>\n",
       "      <th>total</th>\n",
       "      <th>rank</th>\n",
       "      <th>level</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>rank</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A9</td>\n",
       "      <td>80</td>\n",
       "      <td>91</td>\n",
       "      <td>92</td>\n",
       "      <td>263</td>\n",
       "      <td>1</td>\n",
       "      <td>优</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A15</td>\n",
       "      <td>74</td>\n",
       "      <td>89</td>\n",
       "      <td>91</td>\n",
       "      <td>254</td>\n",
       "      <td>2</td>\n",
       "      <td>优</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A4</td>\n",
       "      <td>78</td>\n",
       "      <td>90</td>\n",
       "      <td>85</td>\n",
       "      <td>253</td>\n",
       "      <td>3</td>\n",
       "      <td>优</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>A29</td>\n",
       "      <td>78</td>\n",
       "      <td>88</td>\n",
       "      <td>85</td>\n",
       "      <td>251</td>\n",
       "      <td>4</td>\n",
       "      <td>优</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>A46</td>\n",
       "      <td>72</td>\n",
       "      <td>89</td>\n",
       "      <td>88</td>\n",
       "      <td>249</td>\n",
       "      <td>5</td>\n",
       "      <td>优</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     student  Chinese  Mathematics  English  total  rank level\n",
       "rank                                                          \n",
       "1         A9       80           91       92    263     1     优\n",
       "2        A15       74           89       91    254     2     优\n",
       "3         A4       78           90       85    253     3     优\n",
       "4        A29       78           88       85    251     4     优\n",
       "5        A46       72           89       88    249     5     优"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_df.index=data_df['rank']\n",
    "for i in data_df.index: \n",
    "    if i <= rank_seq[0]:\n",
    "        data_df['level'][i]='优'\n",
    "    elif i <= rank_seq[1]:\n",
    "        data_df['level'][i]='好'\n",
    "    elif i <= rank_seq[2]:\n",
    "        data_df['level'][i]='良'\n",
    "    else:\n",
    "        data_df['level'][i]='需努力'\n",
    "data_df.head()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>student</th>\n",
       "      <th>Chinese</th>\n",
       "      <th>Mathematics</th>\n",
       "      <th>English</th>\n",
       "      <th>total</th>\n",
       "      <th>rank</th>\n",
       "      <th>level</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>rank</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A9</td>\n",
       "      <td>80</td>\n",
       "      <td>91</td>\n",
       "      <td>92</td>\n",
       "      <td>263</td>\n",
       "      <td>1</td>\n",
       "      <td>优</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A15</td>\n",
       "      <td>74</td>\n",
       "      <td>89</td>\n",
       "      <td>91</td>\n",
       "      <td>254</td>\n",
       "      <td>2</td>\n",
       "      <td>优</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A4</td>\n",
       "      <td>78</td>\n",
       "      <td>90</td>\n",
       "      <td>85</td>\n",
       "      <td>253</td>\n",
       "      <td>3</td>\n",
       "      <td>优</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>A29</td>\n",
       "      <td>78</td>\n",
       "      <td>88</td>\n",
       "      <td>85</td>\n",
       "      <td>251</td>\n",
       "      <td>4</td>\n",
       "      <td>优</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>A46</td>\n",
       "      <td>72</td>\n",
       "      <td>89</td>\n",
       "      <td>88</td>\n",
       "      <td>249</td>\n",
       "      <td>5</td>\n",
       "      <td>优</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>A44</td>\n",
       "      <td>73</td>\n",
       "      <td>87</td>\n",
       "      <td>88</td>\n",
       "      <td>248</td>\n",
       "      <td>6</td>\n",
       "      <td>优</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>A41</td>\n",
       "      <td>72</td>\n",
       "      <td>88</td>\n",
       "      <td>88</td>\n",
       "      <td>248</td>\n",
       "      <td>7</td>\n",
       "      <td>优</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>A28</td>\n",
       "      <td>73</td>\n",
       "      <td>84</td>\n",
       "      <td>89</td>\n",
       "      <td>246</td>\n",
       "      <td>8</td>\n",
       "      <td>好</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>A43</td>\n",
       "      <td>62</td>\n",
       "      <td>84</td>\n",
       "      <td>100</td>\n",
       "      <td>246</td>\n",
       "      <td>9</td>\n",
       "      <td>好</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>A5</td>\n",
       "      <td>70</td>\n",
       "      <td>90</td>\n",
       "      <td>85</td>\n",
       "      <td>245</td>\n",
       "      <td>10</td>\n",
       "      <td>好</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>A7</td>\n",
       "      <td>72</td>\n",
       "      <td>88</td>\n",
       "      <td>85</td>\n",
       "      <td>245</td>\n",
       "      <td>11</td>\n",
       "      <td>好</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>A39</td>\n",
       "      <td>76</td>\n",
       "      <td>83</td>\n",
       "      <td>85</td>\n",
       "      <td>244</td>\n",
       "      <td>12</td>\n",
       "      <td>好</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>A40</td>\n",
       "      <td>76</td>\n",
       "      <td>80</td>\n",
       "      <td>88</td>\n",
       "      <td>244</td>\n",
       "      <td>13</td>\n",
       "      <td>好</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>A38</td>\n",
       "      <td>78</td>\n",
       "      <td>79</td>\n",
       "      <td>86</td>\n",
       "      <td>243</td>\n",
       "      <td>14</td>\n",
       "      <td>好</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>A48</td>\n",
       "      <td>73</td>\n",
       "      <td>86</td>\n",
       "      <td>84</td>\n",
       "      <td>243</td>\n",
       "      <td>15</td>\n",
       "      <td>好</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>A8</td>\n",
       "      <td>65</td>\n",
       "      <td>88</td>\n",
       "      <td>89</td>\n",
       "      <td>242</td>\n",
       "      <td>16</td>\n",
       "      <td>好</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>A30</td>\n",
       "      <td>74</td>\n",
       "      <td>79</td>\n",
       "      <td>88</td>\n",
       "      <td>241</td>\n",
       "      <td>17</td>\n",
       "      <td>好</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>A12</td>\n",
       "      <td>70</td>\n",
       "      <td>81</td>\n",
       "      <td>89</td>\n",
       "      <td>240</td>\n",
       "      <td>18</td>\n",
       "      <td>好</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>A24</td>\n",
       "      <td>75</td>\n",
       "      <td>88</td>\n",
       "      <td>75</td>\n",
       "      <td>238</td>\n",
       "      <td>19</td>\n",
       "      <td>好</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>A33</td>\n",
       "      <td>69</td>\n",
       "      <td>78</td>\n",
       "      <td>91</td>\n",
       "      <td>238</td>\n",
       "      <td>20</td>\n",
       "      <td>好</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>A32</td>\n",
       "      <td>68</td>\n",
       "      <td>85</td>\n",
       "      <td>85</td>\n",
       "      <td>238</td>\n",
       "      <td>21</td>\n",
       "      <td>好</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>A31</td>\n",
       "      <td>65</td>\n",
       "      <td>82</td>\n",
       "      <td>90</td>\n",
       "      <td>237</td>\n",
       "      <td>22</td>\n",
       "      <td>好</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>A16</td>\n",
       "      <td>69</td>\n",
       "      <td>84</td>\n",
       "      <td>84</td>\n",
       "      <td>237</td>\n",
       "      <td>23</td>\n",
       "      <td>好</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>A36</td>\n",
       "      <td>65</td>\n",
       "      <td>85</td>\n",
       "      <td>86</td>\n",
       "      <td>236</td>\n",
       "      <td>24</td>\n",
       "      <td>好</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>A11</td>\n",
       "      <td>70</td>\n",
       "      <td>82</td>\n",
       "      <td>83</td>\n",
       "      <td>235</td>\n",
       "      <td>25</td>\n",
       "      <td>良</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>A13</td>\n",
       "      <td>63</td>\n",
       "      <td>84</td>\n",
       "      <td>88</td>\n",
       "      <td>235</td>\n",
       "      <td>26</td>\n",
       "      <td>良</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>A47</td>\n",
       "      <td>71</td>\n",
       "      <td>83</td>\n",
       "      <td>81</td>\n",
       "      <td>235</td>\n",
       "      <td>27</td>\n",
       "      <td>良</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>A14</td>\n",
       "      <td>65</td>\n",
       "      <td>85</td>\n",
       "      <td>85</td>\n",
       "      <td>235</td>\n",
       "      <td>28</td>\n",
       "      <td>良</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>A10</td>\n",
       "      <td>62</td>\n",
       "      <td>84</td>\n",
       "      <td>88</td>\n",
       "      <td>234</td>\n",
       "      <td>29</td>\n",
       "      <td>良</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>A2</td>\n",
       "      <td>67</td>\n",
       "      <td>81</td>\n",
       "      <td>86</td>\n",
       "      <td>234</td>\n",
       "      <td>30</td>\n",
       "      <td>良</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>A34</td>\n",
       "      <td>70</td>\n",
       "      <td>89</td>\n",
       "      <td>75</td>\n",
       "      <td>234</td>\n",
       "      <td>31</td>\n",
       "      <td>良</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>A27</td>\n",
       "      <td>67</td>\n",
       "      <td>78</td>\n",
       "      <td>88</td>\n",
       "      <td>233</td>\n",
       "      <td>32</td>\n",
       "      <td>良</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>A1</td>\n",
       "      <td>71</td>\n",
       "      <td>72</td>\n",
       "      <td>89</td>\n",
       "      <td>232</td>\n",
       "      <td>33</td>\n",
       "      <td>良</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>A22</td>\n",
       "      <td>67</td>\n",
       "      <td>80</td>\n",
       "      <td>81</td>\n",
       "      <td>228</td>\n",
       "      <td>34</td>\n",
       "      <td>良</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>A42</td>\n",
       "      <td>69</td>\n",
       "      <td>74</td>\n",
       "      <td>85</td>\n",
       "      <td>228</td>\n",
       "      <td>35</td>\n",
       "      <td>良</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>A35</td>\n",
       "      <td>64</td>\n",
       "      <td>81</td>\n",
       "      <td>82</td>\n",
       "      <td>227</td>\n",
       "      <td>36</td>\n",
       "      <td>良</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>A26</td>\n",
       "      <td>73</td>\n",
       "      <td>81</td>\n",
       "      <td>72</td>\n",
       "      <td>226</td>\n",
       "      <td>37</td>\n",
       "      <td>良</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>A23</td>\n",
       "      <td>62</td>\n",
       "      <td>81</td>\n",
       "      <td>83</td>\n",
       "      <td>226</td>\n",
       "      <td>38</td>\n",
       "      <td>良</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>A37</td>\n",
       "      <td>61</td>\n",
       "      <td>82</td>\n",
       "      <td>83</td>\n",
       "      <td>226</td>\n",
       "      <td>39</td>\n",
       "      <td>良</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>A45</td>\n",
       "      <td>60</td>\n",
       "      <td>73</td>\n",
       "      <td>93</td>\n",
       "      <td>226</td>\n",
       "      <td>40</td>\n",
       "      <td>良</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>A25</td>\n",
       "      <td>66</td>\n",
       "      <td>82</td>\n",
       "      <td>75</td>\n",
       "      <td>223</td>\n",
       "      <td>41</td>\n",
       "      <td>良</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>A3</td>\n",
       "      <td>60</td>\n",
       "      <td>74</td>\n",
       "      <td>88</td>\n",
       "      <td>222</td>\n",
       "      <td>42</td>\n",
       "      <td>需努力</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>A6</td>\n",
       "      <td>65</td>\n",
       "      <td>70</td>\n",
       "      <td>86</td>\n",
       "      <td>221</td>\n",
       "      <td>43</td>\n",
       "      <td>需努力</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>A21</td>\n",
       "      <td>67</td>\n",
       "      <td>79</td>\n",
       "      <td>72</td>\n",
       "      <td>218</td>\n",
       "      <td>44</td>\n",
       "      <td>需努力</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>A18</td>\n",
       "      <td>68</td>\n",
       "      <td>78</td>\n",
       "      <td>70</td>\n",
       "      <td>216</td>\n",
       "      <td>45</td>\n",
       "      <td>需努力</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>A17</td>\n",
       "      <td>47</td>\n",
       "      <td>82</td>\n",
       "      <td>82</td>\n",
       "      <td>211</td>\n",
       "      <td>46</td>\n",
       "      <td>需努力</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>A20</td>\n",
       "      <td>47</td>\n",
       "      <td>73</td>\n",
       "      <td>83</td>\n",
       "      <td>203</td>\n",
       "      <td>47</td>\n",
       "      <td>需努力</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>A19</td>\n",
       "      <td>43</td>\n",
       "      <td>50</td>\n",
       "      <td>81</td>\n",
       "      <td>174</td>\n",
       "      <td>48</td>\n",
       "      <td>需努力</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     student  Chinese  Mathematics  English  total  rank level\n",
       "rank                                                          \n",
       "1         A9       80           91       92    263     1     优\n",
       "2        A15       74           89       91    254     2     优\n",
       "3         A4       78           90       85    253     3     优\n",
       "4        A29       78           88       85    251     4     优\n",
       "5        A46       72           89       88    249     5     优\n",
       "6        A44       73           87       88    248     6     优\n",
       "7        A41       72           88       88    248     7     优\n",
       "8        A28       73           84       89    246     8     好\n",
       "9        A43       62           84      100    246     9     好\n",
       "10        A5       70           90       85    245    10     好\n",
       "11        A7       72           88       85    245    11     好\n",
       "12       A39       76           83       85    244    12     好\n",
       "13       A40       76           80       88    244    13     好\n",
       "14       A38       78           79       86    243    14     好\n",
       "15       A48       73           86       84    243    15     好\n",
       "16        A8       65           88       89    242    16     好\n",
       "17       A30       74           79       88    241    17     好\n",
       "18       A12       70           81       89    240    18     好\n",
       "19       A24       75           88       75    238    19     好\n",
       "20       A33       69           78       91    238    20     好\n",
       "21       A32       68           85       85    238    21     好\n",
       "22       A31       65           82       90    237    22     好\n",
       "23       A16       69           84       84    237    23     好\n",
       "24       A36       65           85       86    236    24     好\n",
       "25       A11       70           82       83    235    25     良\n",
       "26       A13       63           84       88    235    26     良\n",
       "27       A47       71           83       81    235    27     良\n",
       "28       A14       65           85       85    235    28     良\n",
       "29       A10       62           84       88    234    29     良\n",
       "30        A2       67           81       86    234    30     良\n",
       "31       A34       70           89       75    234    31     良\n",
       "32       A27       67           78       88    233    32     良\n",
       "33        A1       71           72       89    232    33     良\n",
       "34       A22       67           80       81    228    34     良\n",
       "35       A42       69           74       85    228    35     良\n",
       "36       A35       64           81       82    227    36     良\n",
       "37       A26       73           81       72    226    37     良\n",
       "38       A23       62           81       83    226    38     良\n",
       "39       A37       61           82       83    226    39     良\n",
       "40       A45       60           73       93    226    40     良\n",
       "41       A25       66           82       75    223    41     良\n",
       "42        A3       60           74       88    222    42   需努力\n",
       "43        A6       65           70       86    221    43   需努力\n",
       "44       A21       67           79       72    218    44   需努力\n",
       "45       A18       68           78       70    216    45   需努力\n",
       "46       A17       47           82       82    211    46   需努力\n",
       "47       A20       47           73       83    203    47   需努力\n",
       "48       A19       43           50       81    174    48   需努力"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_df.to_excel('result1.xlsx',index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#算法二：使用绩点方法计算名次"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>student</th>\n",
       "      <th>Chinese</th>\n",
       "      <th>Mathematics</th>\n",
       "      <th>English</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A1</td>\n",
       "      <td>71</td>\n",
       "      <td>72</td>\n",
       "      <td>89</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A2</td>\n",
       "      <td>67</td>\n",
       "      <td>81</td>\n",
       "      <td>86</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A3</td>\n",
       "      <td>60</td>\n",
       "      <td>74</td>\n",
       "      <td>88</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A4</td>\n",
       "      <td>78</td>\n",
       "      <td>90</td>\n",
       "      <td>85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>A5</td>\n",
       "      <td>70</td>\n",
       "      <td>90</td>\n",
       "      <td>85</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  student  Chinese  Mathematics  English\n",
       "0      A1       71           72       89\n",
       "1      A2       67           81       86\n",
       "2      A3       60           74       88\n",
       "3      A4       78           90       85\n",
       "4      A5       70           90       85"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_df=pd.read_csv('score.csv')\n",
    "data_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 绩点转换\n",
    "def gpa(score) : \n",
    "    if score >= 90:\n",
    "        return 4.0\n",
    "    elif score >= 85:\n",
    "        return 3.7\n",
    "    elif score >= 82:\n",
    "        return 3.3\n",
    "    elif score >= 79:\n",
    "        return 3.0\n",
    "    elif score >= 75:\n",
    "        return 2.7\n",
    "    elif score >= 72:\n",
    "        return 2.3\n",
    "    elif score >= 69:\n",
    "        return 2.0\n",
    "    elif score >= 65:\n",
    "        return 1.7\n",
    "    elif score >= 62:\n",
    "        return 1.3\n",
    "    elif score >= 60:\n",
    "        return 1.0\n",
    "    else:\n",
    "        return 0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>student</th>\n",
       "      <th>Chinese</th>\n",
       "      <th>Mathematics</th>\n",
       "      <th>English</th>\n",
       "      <th>c</th>\n",
       "      <th>m</th>\n",
       "      <th>e</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A1</td>\n",
       "      <td>71</td>\n",
       "      <td>72</td>\n",
       "      <td>89</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.3</td>\n",
       "      <td>3.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A2</td>\n",
       "      <td>67</td>\n",
       "      <td>81</td>\n",
       "      <td>86</td>\n",
       "      <td>1.7</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A3</td>\n",
       "      <td>60</td>\n",
       "      <td>74</td>\n",
       "      <td>88</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.3</td>\n",
       "      <td>3.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A4</td>\n",
       "      <td>78</td>\n",
       "      <td>90</td>\n",
       "      <td>85</td>\n",
       "      <td>2.7</td>\n",
       "      <td>4.0</td>\n",
       "      <td>3.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>A5</td>\n",
       "      <td>70</td>\n",
       "      <td>90</td>\n",
       "      <td>85</td>\n",
       "      <td>2.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>3.7</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  student  Chinese  Mathematics  English    c    m    e\n",
       "0      A1       71           72       89  2.0  2.3  3.7\n",
       "1      A2       67           81       86  1.7  3.0  3.7\n",
       "2      A3       60           74       88  1.0  2.3  3.7\n",
       "3      A4       78           90       85  2.7  4.0  3.7\n",
       "4      A5       70           90       85  2.0  4.0  3.7"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_df['c'] = data_df['Chinese'].apply(gpa)\n",
    "data_df['m'] = data_df['Mathematics'].apply(gpa)\n",
    "data_df['e'] = data_df['English'].apply(gpa)\n",
    "data_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>student</th>\n",
       "      <th>Chinese</th>\n",
       "      <th>Mathematics</th>\n",
       "      <th>English</th>\n",
       "      <th>c</th>\n",
       "      <th>m</th>\n",
       "      <th>e</th>\n",
       "      <th>GPA</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A1</td>\n",
       "      <td>71</td>\n",
       "      <td>72</td>\n",
       "      <td>89</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.3</td>\n",
       "      <td>3.7</td>\n",
       "      <td>2.666667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A2</td>\n",
       "      <td>67</td>\n",
       "      <td>81</td>\n",
       "      <td>86</td>\n",
       "      <td>1.7</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.7</td>\n",
       "      <td>2.800000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A3</td>\n",
       "      <td>60</td>\n",
       "      <td>74</td>\n",
       "      <td>88</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.3</td>\n",
       "      <td>3.7</td>\n",
       "      <td>2.333333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A4</td>\n",
       "      <td>78</td>\n",
       "      <td>90</td>\n",
       "      <td>85</td>\n",
       "      <td>2.7</td>\n",
       "      <td>4.0</td>\n",
       "      <td>3.7</td>\n",
       "      <td>3.466667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>A5</td>\n",
       "      <td>70</td>\n",
       "      <td>90</td>\n",
       "      <td>85</td>\n",
       "      <td>2.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>3.7</td>\n",
       "      <td>3.233333</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  student  Chinese  Mathematics  English    c    m    e       GPA\n",
       "0      A1       71           72       89  2.0  2.3  3.7  2.666667\n",
       "1      A2       67           81       86  1.7  3.0  3.7  2.800000\n",
       "2      A3       60           74       88  1.0  2.3  3.7  2.333333\n",
       "3      A4       78           90       85  2.7  4.0  3.7  3.466667\n",
       "4      A5       70           90       85  2.0  4.0  3.7  3.233333"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_df['GPA']=data_df[['c', 'm', 'e']].mean(axis=1)\n",
    "data_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>student</th>\n",
       "      <th>Chinese</th>\n",
       "      <th>Mathematics</th>\n",
       "      <th>English</th>\n",
       "      <th>c</th>\n",
       "      <th>m</th>\n",
       "      <th>e</th>\n",
       "      <th>GPA</th>\n",
       "      <th>Rank</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>A9</td>\n",
       "      <td>80</td>\n",
       "      <td>91</td>\n",
       "      <td>92</td>\n",
       "      <td>3.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>3.666667</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A4</td>\n",
       "      <td>78</td>\n",
       "      <td>90</td>\n",
       "      <td>85</td>\n",
       "      <td>2.7</td>\n",
       "      <td>4.0</td>\n",
       "      <td>3.7</td>\n",
       "      <td>3.466667</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>A29</td>\n",
       "      <td>78</td>\n",
       "      <td>88</td>\n",
       "      <td>85</td>\n",
       "      <td>2.7</td>\n",
       "      <td>3.7</td>\n",
       "      <td>3.7</td>\n",
       "      <td>3.366667</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>A15</td>\n",
       "      <td>74</td>\n",
       "      <td>89</td>\n",
       "      <td>91</td>\n",
       "      <td>2.3</td>\n",
       "      <td>3.7</td>\n",
       "      <td>4.0</td>\n",
       "      <td>3.333333</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>A44</td>\n",
       "      <td>73</td>\n",
       "      <td>87</td>\n",
       "      <td>88</td>\n",
       "      <td>2.3</td>\n",
       "      <td>3.7</td>\n",
       "      <td>3.7</td>\n",
       "      <td>3.233333</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   student  Chinese  Mathematics  English    c    m    e       GPA  Rank\n",
       "8       A9       80           91       92  3.0  4.0  4.0  3.666667     1\n",
       "3       A4       78           90       85  2.7  4.0  3.7  3.466667     2\n",
       "28     A29       78           88       85  2.7  3.7  3.7  3.366667     3\n",
       "14     A15       74           89       91  2.3  3.7  4.0  3.333333     4\n",
       "43     A44       73           87       88  2.3  3.7  3.7  3.233333     5"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 最终排序\n",
    "data_df = data_df.sort_values(by='GPA', ascending=False)\n",
    "# 设置名次\n",
    "data_df['Rank']=np.arange(1,len(data_df)+1)\n",
    "data_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>student</th>\n",
       "      <th>Chinese</th>\n",
       "      <th>Mathematics</th>\n",
       "      <th>English</th>\n",
       "      <th>c</th>\n",
       "      <th>m</th>\n",
       "      <th>e</th>\n",
       "      <th>GPA</th>\n",
       "      <th>Rank</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>A9</td>\n",
       "      <td>80</td>\n",
       "      <td>91</td>\n",
       "      <td>92</td>\n",
       "      <td>3.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>3.666667</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A4</td>\n",
       "      <td>78</td>\n",
       "      <td>90</td>\n",
       "      <td>85</td>\n",
       "      <td>2.7</td>\n",
       "      <td>4.0</td>\n",
       "      <td>3.7</td>\n",
       "      <td>3.466667</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>A29</td>\n",
       "      <td>78</td>\n",
       "      <td>88</td>\n",
       "      <td>85</td>\n",
       "      <td>2.7</td>\n",
       "      <td>3.7</td>\n",
       "      <td>3.7</td>\n",
       "      <td>3.366667</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>A15</td>\n",
       "      <td>74</td>\n",
       "      <td>89</td>\n",
       "      <td>91</td>\n",
       "      <td>2.3</td>\n",
       "      <td>3.7</td>\n",
       "      <td>4.0</td>\n",
       "      <td>3.333333</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>A44</td>\n",
       "      <td>73</td>\n",
       "      <td>87</td>\n",
       "      <td>88</td>\n",
       "      <td>2.3</td>\n",
       "      <td>3.7</td>\n",
       "      <td>3.7</td>\n",
       "      <td>3.233333</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>A5</td>\n",
       "      <td>70</td>\n",
       "      <td>90</td>\n",
       "      <td>85</td>\n",
       "      <td>2.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>3.7</td>\n",
       "      <td>3.233333</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>A46</td>\n",
       "      <td>72</td>\n",
       "      <td>89</td>\n",
       "      <td>88</td>\n",
       "      <td>2.3</td>\n",
       "      <td>3.7</td>\n",
       "      <td>3.7</td>\n",
       "      <td>3.233333</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>A7</td>\n",
       "      <td>72</td>\n",
       "      <td>88</td>\n",
       "      <td>85</td>\n",
       "      <td>2.3</td>\n",
       "      <td>3.7</td>\n",
       "      <td>3.7</td>\n",
       "      <td>3.233333</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>A39</td>\n",
       "      <td>76</td>\n",
       "      <td>83</td>\n",
       "      <td>85</td>\n",
       "      <td>2.7</td>\n",
       "      <td>3.3</td>\n",
       "      <td>3.7</td>\n",
       "      <td>3.233333</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>A41</td>\n",
       "      <td>72</td>\n",
       "      <td>88</td>\n",
       "      <td>88</td>\n",
       "      <td>2.3</td>\n",
       "      <td>3.7</td>\n",
       "      <td>3.7</td>\n",
       "      <td>3.233333</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>A38</td>\n",
       "      <td>78</td>\n",
       "      <td>79</td>\n",
       "      <td>86</td>\n",
       "      <td>2.7</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.7</td>\n",
       "      <td>3.133333</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>A40</td>\n",
       "      <td>76</td>\n",
       "      <td>80</td>\n",
       "      <td>88</td>\n",
       "      <td>2.7</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.7</td>\n",
       "      <td>3.133333</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>A28</td>\n",
       "      <td>73</td>\n",
       "      <td>84</td>\n",
       "      <td>89</td>\n",
       "      <td>2.3</td>\n",
       "      <td>3.3</td>\n",
       "      <td>3.7</td>\n",
       "      <td>3.100000</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>A48</td>\n",
       "      <td>73</td>\n",
       "      <td>86</td>\n",
       "      <td>84</td>\n",
       "      <td>2.3</td>\n",
       "      <td>3.7</td>\n",
       "      <td>3.3</td>\n",
       "      <td>3.100000</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>A14</td>\n",
       "      <td>65</td>\n",
       "      <td>85</td>\n",
       "      <td>85</td>\n",
       "      <td>1.7</td>\n",
       "      <td>3.7</td>\n",
       "      <td>3.7</td>\n",
       "      <td>3.033333</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>A36</td>\n",
       "      <td>65</td>\n",
       "      <td>85</td>\n",
       "      <td>86</td>\n",
       "      <td>1.7</td>\n",
       "      <td>3.7</td>\n",
       "      <td>3.7</td>\n",
       "      <td>3.033333</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>A32</td>\n",
       "      <td>68</td>\n",
       "      <td>85</td>\n",
       "      <td>85</td>\n",
       "      <td>1.7</td>\n",
       "      <td>3.7</td>\n",
       "      <td>3.7</td>\n",
       "      <td>3.033333</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>A8</td>\n",
       "      <td>65</td>\n",
       "      <td>88</td>\n",
       "      <td>89</td>\n",
       "      <td>1.7</td>\n",
       "      <td>3.7</td>\n",
       "      <td>3.7</td>\n",
       "      <td>3.033333</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>A24</td>\n",
       "      <td>75</td>\n",
       "      <td>88</td>\n",
       "      <td>75</td>\n",
       "      <td>2.7</td>\n",
       "      <td>3.7</td>\n",
       "      <td>2.7</td>\n",
       "      <td>3.033333</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>A30</td>\n",
       "      <td>74</td>\n",
       "      <td>79</td>\n",
       "      <td>88</td>\n",
       "      <td>2.3</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.7</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>A31</td>\n",
       "      <td>65</td>\n",
       "      <td>82</td>\n",
       "      <td>90</td>\n",
       "      <td>1.7</td>\n",
       "      <td>3.3</td>\n",
       "      <td>4.0</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>A12</td>\n",
       "      <td>70</td>\n",
       "      <td>81</td>\n",
       "      <td>89</td>\n",
       "      <td>2.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.7</td>\n",
       "      <td>2.900000</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>A33</td>\n",
       "      <td>69</td>\n",
       "      <td>78</td>\n",
       "      <td>91</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.7</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2.900000</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>A11</td>\n",
       "      <td>70</td>\n",
       "      <td>82</td>\n",
       "      <td>83</td>\n",
       "      <td>2.0</td>\n",
       "      <td>3.3</td>\n",
       "      <td>3.3</td>\n",
       "      <td>2.866667</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>A16</td>\n",
       "      <td>69</td>\n",
       "      <td>84</td>\n",
       "      <td>84</td>\n",
       "      <td>2.0</td>\n",
       "      <td>3.3</td>\n",
       "      <td>3.3</td>\n",
       "      <td>2.866667</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>A43</td>\n",
       "      <td>62</td>\n",
       "      <td>84</td>\n",
       "      <td>100</td>\n",
       "      <td>1.3</td>\n",
       "      <td>3.3</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2.866667</td>\n",
       "      <td>26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A2</td>\n",
       "      <td>67</td>\n",
       "      <td>81</td>\n",
       "      <td>86</td>\n",
       "      <td>1.7</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.7</td>\n",
       "      <td>2.800000</td>\n",
       "      <td>27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>A34</td>\n",
       "      <td>70</td>\n",
       "      <td>89</td>\n",
       "      <td>75</td>\n",
       "      <td>2.0</td>\n",
       "      <td>3.7</td>\n",
       "      <td>2.7</td>\n",
       "      <td>2.800000</td>\n",
       "      <td>28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>A13</td>\n",
       "      <td>63</td>\n",
       "      <td>84</td>\n",
       "      <td>88</td>\n",
       "      <td>1.3</td>\n",
       "      <td>3.3</td>\n",
       "      <td>3.7</td>\n",
       "      <td>2.766667</td>\n",
       "      <td>29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>A10</td>\n",
       "      <td>62</td>\n",
       "      <td>84</td>\n",
       "      <td>88</td>\n",
       "      <td>1.3</td>\n",
       "      <td>3.3</td>\n",
       "      <td>3.7</td>\n",
       "      <td>2.766667</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>A47</td>\n",
       "      <td>71</td>\n",
       "      <td>83</td>\n",
       "      <td>81</td>\n",
       "      <td>2.0</td>\n",
       "      <td>3.3</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2.766667</td>\n",
       "      <td>31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>A27</td>\n",
       "      <td>67</td>\n",
       "      <td>78</td>\n",
       "      <td>88</td>\n",
       "      <td>1.7</td>\n",
       "      <td>2.7</td>\n",
       "      <td>3.7</td>\n",
       "      <td>2.700000</td>\n",
       "      <td>32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>A42</td>\n",
       "      <td>69</td>\n",
       "      <td>74</td>\n",
       "      <td>85</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.3</td>\n",
       "      <td>3.7</td>\n",
       "      <td>2.666667</td>\n",
       "      <td>33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A1</td>\n",
       "      <td>71</td>\n",
       "      <td>72</td>\n",
       "      <td>89</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.3</td>\n",
       "      <td>3.7</td>\n",
       "      <td>2.666667</td>\n",
       "      <td>34</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>A22</td>\n",
       "      <td>67</td>\n",
       "      <td>80</td>\n",
       "      <td>81</td>\n",
       "      <td>1.7</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2.566667</td>\n",
       "      <td>35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>A25</td>\n",
       "      <td>66</td>\n",
       "      <td>82</td>\n",
       "      <td>75</td>\n",
       "      <td>1.7</td>\n",
       "      <td>3.3</td>\n",
       "      <td>2.7</td>\n",
       "      <td>2.566667</td>\n",
       "      <td>36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>A35</td>\n",
       "      <td>64</td>\n",
       "      <td>81</td>\n",
       "      <td>82</td>\n",
       "      <td>1.3</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.3</td>\n",
       "      <td>2.533333</td>\n",
       "      <td>37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>A26</td>\n",
       "      <td>73</td>\n",
       "      <td>81</td>\n",
       "      <td>72</td>\n",
       "      <td>2.3</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2.3</td>\n",
       "      <td>2.533333</td>\n",
       "      <td>38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>A37</td>\n",
       "      <td>61</td>\n",
       "      <td>82</td>\n",
       "      <td>83</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3.3</td>\n",
       "      <td>3.3</td>\n",
       "      <td>2.533333</td>\n",
       "      <td>39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>A23</td>\n",
       "      <td>62</td>\n",
       "      <td>81</td>\n",
       "      <td>83</td>\n",
       "      <td>1.3</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.3</td>\n",
       "      <td>2.533333</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>A6</td>\n",
       "      <td>65</td>\n",
       "      <td>70</td>\n",
       "      <td>86</td>\n",
       "      <td>1.7</td>\n",
       "      <td>2.0</td>\n",
       "      <td>3.7</td>\n",
       "      <td>2.466667</td>\n",
       "      <td>41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>A45</td>\n",
       "      <td>60</td>\n",
       "      <td>73</td>\n",
       "      <td>93</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.3</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2.433333</td>\n",
       "      <td>42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>A21</td>\n",
       "      <td>67</td>\n",
       "      <td>79</td>\n",
       "      <td>72</td>\n",
       "      <td>1.7</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2.3</td>\n",
       "      <td>2.333333</td>\n",
       "      <td>43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A3</td>\n",
       "      <td>60</td>\n",
       "      <td>74</td>\n",
       "      <td>88</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.3</td>\n",
       "      <td>3.7</td>\n",
       "      <td>2.333333</td>\n",
       "      <td>44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>A17</td>\n",
       "      <td>47</td>\n",
       "      <td>82</td>\n",
       "      <td>82</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.3</td>\n",
       "      <td>3.3</td>\n",
       "      <td>2.200000</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>A18</td>\n",
       "      <td>68</td>\n",
       "      <td>78</td>\n",
       "      <td>70</td>\n",
       "      <td>1.7</td>\n",
       "      <td>2.7</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.133333</td>\n",
       "      <td>46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>A20</td>\n",
       "      <td>47</td>\n",
       "      <td>73</td>\n",
       "      <td>83</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.3</td>\n",
       "      <td>3.3</td>\n",
       "      <td>1.866667</td>\n",
       "      <td>47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>A19</td>\n",
       "      <td>43</td>\n",
       "      <td>50</td>\n",
       "      <td>81</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>48</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   student  Chinese  Mathematics  English    c    m    e       GPA  Rank\n",
       "8       A9       80           91       92  3.0  4.0  4.0  3.666667     1\n",
       "3       A4       78           90       85  2.7  4.0  3.7  3.466667     2\n",
       "28     A29       78           88       85  2.7  3.7  3.7  3.366667     3\n",
       "14     A15       74           89       91  2.3  3.7  4.0  3.333333     4\n",
       "43     A44       73           87       88  2.3  3.7  3.7  3.233333     5\n",
       "4       A5       70           90       85  2.0  4.0  3.7  3.233333     6\n",
       "45     A46       72           89       88  2.3  3.7  3.7  3.233333     7\n",
       "6       A7       72           88       85  2.3  3.7  3.7  3.233333     8\n",
       "38     A39       76           83       85  2.7  3.3  3.7  3.233333     9\n",
       "40     A41       72           88       88  2.3  3.7  3.7  3.233333    10\n",
       "37     A38       78           79       86  2.7  3.0  3.7  3.133333    11\n",
       "39     A40       76           80       88  2.7  3.0  3.7  3.133333    12\n",
       "27     A28       73           84       89  2.3  3.3  3.7  3.100000    13\n",
       "47     A48       73           86       84  2.3  3.7  3.3  3.100000    14\n",
       "13     A14       65           85       85  1.7  3.7  3.7  3.033333    15\n",
       "35     A36       65           85       86  1.7  3.7  3.7  3.033333    16\n",
       "31     A32       68           85       85  1.7  3.7  3.7  3.033333    17\n",
       "7       A8       65           88       89  1.7  3.7  3.7  3.033333    18\n",
       "23     A24       75           88       75  2.7  3.7  2.7  3.033333    19\n",
       "29     A30       74           79       88  2.3  3.0  3.7  3.000000    20\n",
       "30     A31       65           82       90  1.7  3.3  4.0  3.000000    21\n",
       "11     A12       70           81       89  2.0  3.0  3.7  2.900000    22\n",
       "32     A33       69           78       91  2.0  2.7  4.0  2.900000    23\n",
       "10     A11       70           82       83  2.0  3.3  3.3  2.866667    24\n",
       "15     A16       69           84       84  2.0  3.3  3.3  2.866667    25\n",
       "42     A43       62           84      100  1.3  3.3  4.0  2.866667    26\n",
       "1       A2       67           81       86  1.7  3.0  3.7  2.800000    27\n",
       "33     A34       70           89       75  2.0  3.7  2.7  2.800000    28\n",
       "12     A13       63           84       88  1.3  3.3  3.7  2.766667    29\n",
       "9      A10       62           84       88  1.3  3.3  3.7  2.766667    30\n",
       "46     A47       71           83       81  2.0  3.3  3.0  2.766667    31\n",
       "26     A27       67           78       88  1.7  2.7  3.7  2.700000    32\n",
       "41     A42       69           74       85  2.0  2.3  3.7  2.666667    33\n",
       "0       A1       71           72       89  2.0  2.3  3.7  2.666667    34\n",
       "21     A22       67           80       81  1.7  3.0  3.0  2.566667    35\n",
       "24     A25       66           82       75  1.7  3.3  2.7  2.566667    36\n",
       "34     A35       64           81       82  1.3  3.0  3.3  2.533333    37\n",
       "25     A26       73           81       72  2.3  3.0  2.3  2.533333    38\n",
       "36     A37       61           82       83  1.0  3.3  3.3  2.533333    39\n",
       "22     A23       62           81       83  1.3  3.0  3.3  2.533333    40\n",
       "5       A6       65           70       86  1.7  2.0  3.7  2.466667    41\n",
       "44     A45       60           73       93  1.0  2.3  4.0  2.433333    42\n",
       "20     A21       67           79       72  1.7  3.0  2.3  2.333333    43\n",
       "2       A3       60           74       88  1.0  2.3  3.7  2.333333    44\n",
       "16     A17       47           82       82  0.0  3.3  3.3  2.200000    45\n",
       "17     A18       68           78       70  1.7  2.7  2.0  2.133333    46\n",
       "19     A20       47           73       83  0.0  2.3  3.3  1.866667    47\n",
       "18     A19       43           50       81  0.0  0.0  3.0  1.000000    48"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_df.to_excel('result2.xlsx',index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#算法三：按照分科排名"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>student</th>\n",
       "      <th>Chinese</th>\n",
       "      <th>Mathematics</th>\n",
       "      <th>English</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A1</td>\n",
       "      <td>71</td>\n",
       "      <td>72</td>\n",
       "      <td>89</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A2</td>\n",
       "      <td>67</td>\n",
       "      <td>81</td>\n",
       "      <td>86</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A3</td>\n",
       "      <td>60</td>\n",
       "      <td>74</td>\n",
       "      <td>88</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A4</td>\n",
       "      <td>78</td>\n",
       "      <td>90</td>\n",
       "      <td>85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>A5</td>\n",
       "      <td>70</td>\n",
       "      <td>90</td>\n",
       "      <td>85</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  student  Chinese  Mathematics  English\n",
       "0      A1       71           72       89\n",
       "1      A2       67           81       86\n",
       "2      A3       60           74       88\n",
       "3      A4       78           90       85\n",
       "4      A5       70           90       85"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_df=pd.read_csv('score.csv')\n",
    "data_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>student</th>\n",
       "      <th>Chinese</th>\n",
       "      <th>Mathematics</th>\n",
       "      <th>English</th>\n",
       "      <th>c_rank</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>A9</td>\n",
       "      <td>80</td>\n",
       "      <td>91</td>\n",
       "      <td>92</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A4</td>\n",
       "      <td>78</td>\n",
       "      <td>90</td>\n",
       "      <td>85</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>A29</td>\n",
       "      <td>78</td>\n",
       "      <td>88</td>\n",
       "      <td>85</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>A38</td>\n",
       "      <td>78</td>\n",
       "      <td>79</td>\n",
       "      <td>86</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>A39</td>\n",
       "      <td>76</td>\n",
       "      <td>83</td>\n",
       "      <td>85</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   student  Chinese  Mathematics  English  c_rank\n",
       "8       A9       80           91       92       1\n",
       "3       A4       78           90       85       2\n",
       "28     A29       78           88       85       3\n",
       "37     A38       78           79       86       4\n",
       "38     A39       76           83       85       5"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 语文排序\n",
    "data_df = data_df.sort_values(by='Chinese',ascending=False)\n",
    "# 设置名次\n",
    "data_df['c_rank']=np.arange(1,len(data_df)+1)\n",
    "data_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>student</th>\n",
       "      <th>Chinese</th>\n",
       "      <th>Mathematics</th>\n",
       "      <th>English</th>\n",
       "      <th>c_rank</th>\n",
       "      <th>m_rank</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>A9</td>\n",
       "      <td>80</td>\n",
       "      <td>91</td>\n",
       "      <td>92</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A4</td>\n",
       "      <td>78</td>\n",
       "      <td>90</td>\n",
       "      <td>85</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>A5</td>\n",
       "      <td>70</td>\n",
       "      <td>90</td>\n",
       "      <td>85</td>\n",
       "      <td>20</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>A15</td>\n",
       "      <td>74</td>\n",
       "      <td>89</td>\n",
       "      <td>91</td>\n",
       "      <td>8</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>A34</td>\n",
       "      <td>70</td>\n",
       "      <td>89</td>\n",
       "      <td>75</td>\n",
       "      <td>19</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   student  Chinese  Mathematics  English  c_rank  m_rank\n",
       "8       A9       80           91       92       1       1\n",
       "3       A4       78           90       85       2       2\n",
       "4       A5       70           90       85      20       3\n",
       "14     A15       74           89       91       8       4\n",
       "33     A34       70           89       75      19       5"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 数学排序\n",
    "data_df = data_df.sort_values(by='Mathematics',ascending=False)\n",
    "# 设置名次\n",
    "data_df['m_rank']=np.arange(1,len(data_df)+1)\n",
    "data_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>student</th>\n",
       "      <th>Chinese</th>\n",
       "      <th>Mathematics</th>\n",
       "      <th>English</th>\n",
       "      <th>c_rank</th>\n",
       "      <th>m_rank</th>\n",
       "      <th>e_rank</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>A43</td>\n",
       "      <td>62</td>\n",
       "      <td>84</td>\n",
       "      <td>100</td>\n",
       "      <td>41</td>\n",
       "      <td>18</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>A45</td>\n",
       "      <td>60</td>\n",
       "      <td>73</td>\n",
       "      <td>93</td>\n",
       "      <td>44</td>\n",
       "      <td>44</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>A9</td>\n",
       "      <td>80</td>\n",
       "      <td>91</td>\n",
       "      <td>92</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>A33</td>\n",
       "      <td>69</td>\n",
       "      <td>78</td>\n",
       "      <td>91</td>\n",
       "      <td>25</td>\n",
       "      <td>39</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>A15</td>\n",
       "      <td>74</td>\n",
       "      <td>89</td>\n",
       "      <td>91</td>\n",
       "      <td>8</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   student  Chinese  Mathematics  English  c_rank  m_rank  e_rank\n",
       "42     A43       62           84      100      41      18       1\n",
       "44     A45       60           73       93      44      44       2\n",
       "8       A9       80           91       92       1       1       3\n",
       "32     A33       69           78       91      25      39       4\n",
       "14     A15       74           89       91       8       4       5"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 英语排序\n",
    "data_df = data_df.sort_values(by='English',ascending=False)\n",
    "# 设置名次\n",
    "data_df['e_rank']=np.arange(1,len(data_df)+1)\n",
    "data_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>student</th>\n",
       "      <th>Chinese</th>\n",
       "      <th>Mathematics</th>\n",
       "      <th>English</th>\n",
       "      <th>c_rank</th>\n",
       "      <th>m_rank</th>\n",
       "      <th>e_rank</th>\n",
       "      <th>avg_rank</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>A43</td>\n",
       "      <td>62</td>\n",
       "      <td>84</td>\n",
       "      <td>100</td>\n",
       "      <td>41</td>\n",
       "      <td>18</td>\n",
       "      <td>1</td>\n",
       "      <td>20.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>A45</td>\n",
       "      <td>60</td>\n",
       "      <td>73</td>\n",
       "      <td>93</td>\n",
       "      <td>44</td>\n",
       "      <td>44</td>\n",
       "      <td>2</td>\n",
       "      <td>30.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>A9</td>\n",
       "      <td>80</td>\n",
       "      <td>91</td>\n",
       "      <td>92</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>1.666667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>A33</td>\n",
       "      <td>69</td>\n",
       "      <td>78</td>\n",
       "      <td>91</td>\n",
       "      <td>25</td>\n",
       "      <td>39</td>\n",
       "      <td>4</td>\n",
       "      <td>22.666667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>A15</td>\n",
       "      <td>74</td>\n",
       "      <td>89</td>\n",
       "      <td>91</td>\n",
       "      <td>8</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>5.666667</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   student  Chinese  Mathematics  English  c_rank  m_rank  e_rank   avg_rank\n",
       "42     A43       62           84      100      41      18       1  20.000000\n",
       "44     A45       60           73       93      44      44       2  30.000000\n",
       "8       A9       80           91       92       1       1       3   1.666667\n",
       "32     A33       69           78       91      25      39       4  22.666667\n",
       "14     A15       74           89       91       8       4       5   5.666667"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 均值计算\n",
    "data_df['avg_rank']=data_df[['c_rank', 'm_rank', 'e_rank']].mean(axis=1)\n",
    "data_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>student</th>\n",
       "      <th>Chinese</th>\n",
       "      <th>Mathematics</th>\n",
       "      <th>English</th>\n",
       "      <th>c_rank</th>\n",
       "      <th>m_rank</th>\n",
       "      <th>e_rank</th>\n",
       "      <th>avg_rank</th>\n",
       "      <th>Rank</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>A9</td>\n",
       "      <td>80</td>\n",
       "      <td>91</td>\n",
       "      <td>92</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>1.666667</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>A15</td>\n",
       "      <td>74</td>\n",
       "      <td>89</td>\n",
       "      <td>91</td>\n",
       "      <td>8</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>5.666667</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A4</td>\n",
       "      <td>78</td>\n",
       "      <td>90</td>\n",
       "      <td>85</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>28</td>\n",
       "      <td>10.666667</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>A41</td>\n",
       "      <td>72</td>\n",
       "      <td>88</td>\n",
       "      <td>88</td>\n",
       "      <td>14</td>\n",
       "      <td>10</td>\n",
       "      <td>11</td>\n",
       "      <td>11.666667</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>A46</td>\n",
       "      <td>72</td>\n",
       "      <td>89</td>\n",
       "      <td>88</td>\n",
       "      <td>16</td>\n",
       "      <td>6</td>\n",
       "      <td>15</td>\n",
       "      <td>12.333333</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   student  Chinese  Mathematics  English  c_rank  m_rank  e_rank   avg_rank  \\\n",
       "8       A9       80           91       92       1       1       3   1.666667   \n",
       "14     A15       74           89       91       8       4       5   5.666667   \n",
       "3       A4       78           90       85       2       2      28  10.666667   \n",
       "40     A41       72           88       88      14      10      11  11.666667   \n",
       "45     A46       72           89       88      16       6      15  12.333333   \n",
       "\n",
       "    Rank  \n",
       "8      1  \n",
       "14     2  \n",
       "3      3  \n",
       "40     4  \n",
       "45     5  "
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 最终排序\n",
    "data_df = data_df.sort_values(by='avg_rank')\n",
    "# 设置名次\n",
    "data_df['Rank']=np.arange(1,len(data_df)+1)\n",
    "data_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>student</th>\n",
       "      <th>Chinese</th>\n",
       "      <th>Mathematics</th>\n",
       "      <th>English</th>\n",
       "      <th>c_rank</th>\n",
       "      <th>m_rank</th>\n",
       "      <th>e_rank</th>\n",
       "      <th>avg_rank</th>\n",
       "      <th>Rank</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>A9</td>\n",
       "      <td>80</td>\n",
       "      <td>91</td>\n",
       "      <td>92</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>1.666667</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>A15</td>\n",
       "      <td>74</td>\n",
       "      <td>89</td>\n",
       "      <td>91</td>\n",
       "      <td>8</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>5.666667</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A4</td>\n",
       "      <td>78</td>\n",
       "      <td>90</td>\n",
       "      <td>85</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>28</td>\n",
       "      <td>10.666667</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>A41</td>\n",
       "      <td>72</td>\n",
       "      <td>88</td>\n",
       "      <td>88</td>\n",
       "      <td>14</td>\n",
       "      <td>10</td>\n",
       "      <td>11</td>\n",
       "      <td>11.666667</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>A46</td>\n",
       "      <td>72</td>\n",
       "      <td>89</td>\n",
       "      <td>88</td>\n",
       "      <td>16</td>\n",
       "      <td>6</td>\n",
       "      <td>15</td>\n",
       "      <td>12.333333</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>A28</td>\n",
       "      <td>73</td>\n",
       "      <td>84</td>\n",
       "      <td>89</td>\n",
       "      <td>10</td>\n",
       "      <td>19</td>\n",
       "      <td>9</td>\n",
       "      <td>12.666667</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>A29</td>\n",
       "      <td>78</td>\n",
       "      <td>88</td>\n",
       "      <td>85</td>\n",
       "      <td>3</td>\n",
       "      <td>7</td>\n",
       "      <td>29</td>\n",
       "      <td>13.000000</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>A44</td>\n",
       "      <td>73</td>\n",
       "      <td>87</td>\n",
       "      <td>88</td>\n",
       "      <td>12</td>\n",
       "      <td>12</td>\n",
       "      <td>19</td>\n",
       "      <td>14.333333</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>A5</td>\n",
       "      <td>70</td>\n",
       "      <td>90</td>\n",
       "      <td>85</td>\n",
       "      <td>20</td>\n",
       "      <td>3</td>\n",
       "      <td>25</td>\n",
       "      <td>16.000000</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>A8</td>\n",
       "      <td>65</td>\n",
       "      <td>88</td>\n",
       "      <td>89</td>\n",
       "      <td>36</td>\n",
       "      <td>9</td>\n",
       "      <td>7</td>\n",
       "      <td>17.333333</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>A40</td>\n",
       "      <td>76</td>\n",
       "      <td>80</td>\n",
       "      <td>88</td>\n",
       "      <td>6</td>\n",
       "      <td>35</td>\n",
       "      <td>12</td>\n",
       "      <td>17.666667</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>A7</td>\n",
       "      <td>72</td>\n",
       "      <td>88</td>\n",
       "      <td>85</td>\n",
       "      <td>15</td>\n",
       "      <td>11</td>\n",
       "      <td>27</td>\n",
       "      <td>17.666667</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>A39</td>\n",
       "      <td>76</td>\n",
       "      <td>83</td>\n",
       "      <td>85</td>\n",
       "      <td>5</td>\n",
       "      <td>23</td>\n",
       "      <td>26</td>\n",
       "      <td>18.000000</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>A48</td>\n",
       "      <td>73</td>\n",
       "      <td>86</td>\n",
       "      <td>84</td>\n",
       "      <td>11</td>\n",
       "      <td>13</td>\n",
       "      <td>32</td>\n",
       "      <td>18.666667</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>A24</td>\n",
       "      <td>75</td>\n",
       "      <td>88</td>\n",
       "      <td>75</td>\n",
       "      <td>7</td>\n",
       "      <td>8</td>\n",
       "      <td>44</td>\n",
       "      <td>19.666667</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>A43</td>\n",
       "      <td>62</td>\n",
       "      <td>84</td>\n",
       "      <td>100</td>\n",
       "      <td>41</td>\n",
       "      <td>18</td>\n",
       "      <td>1</td>\n",
       "      <td>20.000000</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>A12</td>\n",
       "      <td>70</td>\n",
       "      <td>81</td>\n",
       "      <td>89</td>\n",
       "      <td>22</td>\n",
       "      <td>32</td>\n",
       "      <td>8</td>\n",
       "      <td>20.666667</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>A30</td>\n",
       "      <td>74</td>\n",
       "      <td>79</td>\n",
       "      <td>88</td>\n",
       "      <td>9</td>\n",
       "      <td>37</td>\n",
       "      <td>17</td>\n",
       "      <td>21.000000</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>A38</td>\n",
       "      <td>78</td>\n",
       "      <td>79</td>\n",
       "      <td>86</td>\n",
       "      <td>4</td>\n",
       "      <td>38</td>\n",
       "      <td>21</td>\n",
       "      <td>21.000000</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>A31</td>\n",
       "      <td>65</td>\n",
       "      <td>82</td>\n",
       "      <td>90</td>\n",
       "      <td>33</td>\n",
       "      <td>26</td>\n",
       "      <td>6</td>\n",
       "      <td>21.666667</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>A32</td>\n",
       "      <td>68</td>\n",
       "      <td>85</td>\n",
       "      <td>85</td>\n",
       "      <td>26</td>\n",
       "      <td>16</td>\n",
       "      <td>24</td>\n",
       "      <td>22.000000</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>A33</td>\n",
       "      <td>69</td>\n",
       "      <td>78</td>\n",
       "      <td>91</td>\n",
       "      <td>25</td>\n",
       "      <td>39</td>\n",
       "      <td>4</td>\n",
       "      <td>22.666667</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>A34</td>\n",
       "      <td>70</td>\n",
       "      <td>89</td>\n",
       "      <td>75</td>\n",
       "      <td>19</td>\n",
       "      <td>5</td>\n",
       "      <td>45</td>\n",
       "      <td>23.000000</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>A36</td>\n",
       "      <td>65</td>\n",
       "      <td>85</td>\n",
       "      <td>86</td>\n",
       "      <td>34</td>\n",
       "      <td>15</td>\n",
       "      <td>20</td>\n",
       "      <td>23.000000</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>A13</td>\n",
       "      <td>63</td>\n",
       "      <td>84</td>\n",
       "      <td>88</td>\n",
       "      <td>39</td>\n",
       "      <td>17</td>\n",
       "      <td>18</td>\n",
       "      <td>24.666667</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A1</td>\n",
       "      <td>71</td>\n",
       "      <td>72</td>\n",
       "      <td>89</td>\n",
       "      <td>18</td>\n",
       "      <td>46</td>\n",
       "      <td>10</td>\n",
       "      <td>24.666667</td>\n",
       "      <td>26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>A10</td>\n",
       "      <td>62</td>\n",
       "      <td>84</td>\n",
       "      <td>88</td>\n",
       "      <td>40</td>\n",
       "      <td>20</td>\n",
       "      <td>16</td>\n",
       "      <td>25.333333</td>\n",
       "      <td>27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>A16</td>\n",
       "      <td>69</td>\n",
       "      <td>84</td>\n",
       "      <td>84</td>\n",
       "      <td>24</td>\n",
       "      <td>21</td>\n",
       "      <td>33</td>\n",
       "      <td>26.000000</td>\n",
       "      <td>28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>A14</td>\n",
       "      <td>65</td>\n",
       "      <td>85</td>\n",
       "      <td>85</td>\n",
       "      <td>35</td>\n",
       "      <td>14</td>\n",
       "      <td>30</td>\n",
       "      <td>26.333333</td>\n",
       "      <td>29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>A47</td>\n",
       "      <td>71</td>\n",
       "      <td>83</td>\n",
       "      <td>81</td>\n",
       "      <td>17</td>\n",
       "      <td>22</td>\n",
       "      <td>41</td>\n",
       "      <td>26.666667</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A2</td>\n",
       "      <td>67</td>\n",
       "      <td>81</td>\n",
       "      <td>86</td>\n",
       "      <td>30</td>\n",
       "      <td>29</td>\n",
       "      <td>22</td>\n",
       "      <td>27.000000</td>\n",
       "      <td>31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>A11</td>\n",
       "      <td>70</td>\n",
       "      <td>82</td>\n",
       "      <td>83</td>\n",
       "      <td>21</td>\n",
       "      <td>28</td>\n",
       "      <td>34</td>\n",
       "      <td>27.666667</td>\n",
       "      <td>32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>A27</td>\n",
       "      <td>67</td>\n",
       "      <td>78</td>\n",
       "      <td>88</td>\n",
       "      <td>31</td>\n",
       "      <td>40</td>\n",
       "      <td>13</td>\n",
       "      <td>28.000000</td>\n",
       "      <td>33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>A45</td>\n",
       "      <td>60</td>\n",
       "      <td>73</td>\n",
       "      <td>93</td>\n",
       "      <td>44</td>\n",
       "      <td>44</td>\n",
       "      <td>2</td>\n",
       "      <td>30.000000</td>\n",
       "      <td>34</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>A26</td>\n",
       "      <td>73</td>\n",
       "      <td>81</td>\n",
       "      <td>72</td>\n",
       "      <td>13</td>\n",
       "      <td>33</td>\n",
       "      <td>47</td>\n",
       "      <td>31.000000</td>\n",
       "      <td>35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>A42</td>\n",
       "      <td>69</td>\n",
       "      <td>74</td>\n",
       "      <td>85</td>\n",
       "      <td>23</td>\n",
       "      <td>42</td>\n",
       "      <td>31</td>\n",
       "      <td>32.000000</td>\n",
       "      <td>36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A3</td>\n",
       "      <td>60</td>\n",
       "      <td>74</td>\n",
       "      <td>88</td>\n",
       "      <td>45</td>\n",
       "      <td>43</td>\n",
       "      <td>14</td>\n",
       "      <td>34.000000</td>\n",
       "      <td>37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>A25</td>\n",
       "      <td>66</td>\n",
       "      <td>82</td>\n",
       "      <td>75</td>\n",
       "      <td>32</td>\n",
       "      <td>27</td>\n",
       "      <td>43</td>\n",
       "      <td>34.000000</td>\n",
       "      <td>38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>A22</td>\n",
       "      <td>67</td>\n",
       "      <td>80</td>\n",
       "      <td>81</td>\n",
       "      <td>29</td>\n",
       "      <td>34</td>\n",
       "      <td>40</td>\n",
       "      <td>34.333333</td>\n",
       "      <td>39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>A37</td>\n",
       "      <td>61</td>\n",
       "      <td>82</td>\n",
       "      <td>83</td>\n",
       "      <td>43</td>\n",
       "      <td>24</td>\n",
       "      <td>37</td>\n",
       "      <td>34.666667</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>A6</td>\n",
       "      <td>65</td>\n",
       "      <td>70</td>\n",
       "      <td>86</td>\n",
       "      <td>37</td>\n",
       "      <td>47</td>\n",
       "      <td>23</td>\n",
       "      <td>35.666667</td>\n",
       "      <td>41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>A35</td>\n",
       "      <td>64</td>\n",
       "      <td>81</td>\n",
       "      <td>82</td>\n",
       "      <td>38</td>\n",
       "      <td>30</td>\n",
       "      <td>39</td>\n",
       "      <td>35.666667</td>\n",
       "      <td>42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>A23</td>\n",
       "      <td>62</td>\n",
       "      <td>81</td>\n",
       "      <td>83</td>\n",
       "      <td>42</td>\n",
       "      <td>31</td>\n",
       "      <td>35</td>\n",
       "      <td>36.000000</td>\n",
       "      <td>43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>A17</td>\n",
       "      <td>47</td>\n",
       "      <td>82</td>\n",
       "      <td>82</td>\n",
       "      <td>47</td>\n",
       "      <td>25</td>\n",
       "      <td>38</td>\n",
       "      <td>36.666667</td>\n",
       "      <td>44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>A21</td>\n",
       "      <td>67</td>\n",
       "      <td>79</td>\n",
       "      <td>72</td>\n",
       "      <td>28</td>\n",
       "      <td>36</td>\n",
       "      <td>46</td>\n",
       "      <td>36.666667</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>A18</td>\n",
       "      <td>68</td>\n",
       "      <td>78</td>\n",
       "      <td>70</td>\n",
       "      <td>27</td>\n",
       "      <td>41</td>\n",
       "      <td>48</td>\n",
       "      <td>38.666667</td>\n",
       "      <td>46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>A20</td>\n",
       "      <td>47</td>\n",
       "      <td>73</td>\n",
       "      <td>83</td>\n",
       "      <td>46</td>\n",
       "      <td>45</td>\n",
       "      <td>36</td>\n",
       "      <td>42.333333</td>\n",
       "      <td>47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>A19</td>\n",
       "      <td>43</td>\n",
       "      <td>50</td>\n",
       "      <td>81</td>\n",
       "      <td>48</td>\n",
       "      <td>48</td>\n",
       "      <td>42</td>\n",
       "      <td>46.000000</td>\n",
       "      <td>48</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   student  Chinese  Mathematics  English  c_rank  m_rank  e_rank   avg_rank  \\\n",
       "8       A9       80           91       92       1       1       3   1.666667   \n",
       "14     A15       74           89       91       8       4       5   5.666667   \n",
       "3       A4       78           90       85       2       2      28  10.666667   \n",
       "40     A41       72           88       88      14      10      11  11.666667   \n",
       "45     A46       72           89       88      16       6      15  12.333333   \n",
       "27     A28       73           84       89      10      19       9  12.666667   \n",
       "28     A29       78           88       85       3       7      29  13.000000   \n",
       "43     A44       73           87       88      12      12      19  14.333333   \n",
       "4       A5       70           90       85      20       3      25  16.000000   \n",
       "7       A8       65           88       89      36       9       7  17.333333   \n",
       "39     A40       76           80       88       6      35      12  17.666667   \n",
       "6       A7       72           88       85      15      11      27  17.666667   \n",
       "38     A39       76           83       85       5      23      26  18.000000   \n",
       "47     A48       73           86       84      11      13      32  18.666667   \n",
       "23     A24       75           88       75       7       8      44  19.666667   \n",
       "42     A43       62           84      100      41      18       1  20.000000   \n",
       "11     A12       70           81       89      22      32       8  20.666667   \n",
       "29     A30       74           79       88       9      37      17  21.000000   \n",
       "37     A38       78           79       86       4      38      21  21.000000   \n",
       "30     A31       65           82       90      33      26       6  21.666667   \n",
       "31     A32       68           85       85      26      16      24  22.000000   \n",
       "32     A33       69           78       91      25      39       4  22.666667   \n",
       "33     A34       70           89       75      19       5      45  23.000000   \n",
       "35     A36       65           85       86      34      15      20  23.000000   \n",
       "12     A13       63           84       88      39      17      18  24.666667   \n",
       "0       A1       71           72       89      18      46      10  24.666667   \n",
       "9      A10       62           84       88      40      20      16  25.333333   \n",
       "15     A16       69           84       84      24      21      33  26.000000   \n",
       "13     A14       65           85       85      35      14      30  26.333333   \n",
       "46     A47       71           83       81      17      22      41  26.666667   \n",
       "1       A2       67           81       86      30      29      22  27.000000   \n",
       "10     A11       70           82       83      21      28      34  27.666667   \n",
       "26     A27       67           78       88      31      40      13  28.000000   \n",
       "44     A45       60           73       93      44      44       2  30.000000   \n",
       "25     A26       73           81       72      13      33      47  31.000000   \n",
       "41     A42       69           74       85      23      42      31  32.000000   \n",
       "2       A3       60           74       88      45      43      14  34.000000   \n",
       "24     A25       66           82       75      32      27      43  34.000000   \n",
       "21     A22       67           80       81      29      34      40  34.333333   \n",
       "36     A37       61           82       83      43      24      37  34.666667   \n",
       "5       A6       65           70       86      37      47      23  35.666667   \n",
       "34     A35       64           81       82      38      30      39  35.666667   \n",
       "22     A23       62           81       83      42      31      35  36.000000   \n",
       "16     A17       47           82       82      47      25      38  36.666667   \n",
       "20     A21       67           79       72      28      36      46  36.666667   \n",
       "17     A18       68           78       70      27      41      48  38.666667   \n",
       "19     A20       47           73       83      46      45      36  42.333333   \n",
       "18     A19       43           50       81      48      48      42  46.000000   \n",
       "\n",
       "    Rank  \n",
       "8      1  \n",
       "14     2  \n",
       "3      3  \n",
       "40     4  \n",
       "45     5  \n",
       "27     6  \n",
       "28     7  \n",
       "43     8  \n",
       "4      9  \n",
       "7     10  \n",
       "39    11  \n",
       "6     12  \n",
       "38    13  \n",
       "47    14  \n",
       "23    15  \n",
       "42    16  \n",
       "11    17  \n",
       "29    18  \n",
       "37    19  \n",
       "30    20  \n",
       "31    21  \n",
       "32    22  \n",
       "33    23  \n",
       "35    24  \n",
       "12    25  \n",
       "0     26  \n",
       "9     27  \n",
       "15    28  \n",
       "13    29  \n",
       "46    30  \n",
       "1     31  \n",
       "10    32  \n",
       "26    33  \n",
       "44    34  \n",
       "25    35  \n",
       "41    36  \n",
       "2     37  \n",
       "24    38  \n",
       "21    39  \n",
       "36    40  \n",
       "5     41  \n",
       "34    42  \n",
       "22    43  \n",
       "16    44  \n",
       "20    45  \n",
       "17    46  \n",
       "19    47  \n",
       "18    48  "
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_df.to_excel('result3.xlsx',index=False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.12.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
